# server/app.py
from datetime import datetime, timezone, timedelta, time
import pytz
# 在所有需要获取当前时间的地方，统一使用：
# current_time = get_china_time()
from apscheduler.schedulers.background import BackgroundScheduler
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS
import pandas as pd
import os
from dotenv import load_dotenv
from sms_sender import send_sms
from werkzeug.utils import secure_filename
# 引用盐值加密密码
import bcrypt
# 新增正则表达式库用于邮箱格式校验
import re
# 导入 SQLAlchemy 的 IntegrityError 异常类
from sqlalchemy.exc import IntegrityError
# SQLAlchemy 用于生成 SQL 函数的工具
from sqlalchemy import func
# 新增JWT相关导入
from flask_jwt_extended import JWTManager, create_access_token, get_jwt_identity, jwt_required
import base64
import requests
from server import config
# 在吐槽圈发布图片防伪造的方法中使用
from urllib.parse import urlparse
import json
from collections import defaultdict
# 加载环境变量
load_dotenv()

app = Flask(__name__)
# 加载二级server中的config配置
app.config.from_object('server.config.Config')
CORS(app)  # 允许所有跨域请求
# ============== JWT配置新增开始 ==============
app.config["JWT_SECRET_KEY"] = os.getenv('JWT_SECRET', 'super-secret-key')  # 从环境变量获取密钥
# app.config["JWT_ACCESS_TOKEN_EXPIRES"] = 3600  # Token有效期1小时（秒）
# 禁用过期时间（需谨慎，仅用于演示需求）
app.config["JWT_ACCESS_TOKEN_EXPIRES"] = False  # 覆盖之前的3600配置
jwt = JWTManager(app)  # 初始化JWT扩展
# ============== JWT配置新增结束 ==============
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['ALLOWED_EXTENSIONS'] = {'xlsx', 'xls'}
app.config[
    'SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_NAME')}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# app.config['ALLOWED_IMAGE_EXTENSIONS'] = {'png', 'jpg', 'jpeg', 'gif'}
# app.config['MAX_IMAGE_SIZE'] = 10 * 1024 * 1024  # 10MB

db = SQLAlchemy(app)
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)


# 定义数据模型
class Customer(db.Model):
    __tablename__ = 'customer'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    expire_date = db.Column(db.Date, nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    notified = db.Column(db.Boolean, default=False)  # 对应tinyint(1)
    employee_number = db.Column(db.String(20), nullable=False)
    sms_reminded = db.Column(db.Boolean, default=False)


def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']


# 上传文本POST接口
@app.route('/api/upload', methods=['POST'])
def upload_file():
    # 获取前端传递的员工号
    employee_number = request.form.get('employee_number')  # 从form-data获取

    # 验证员工号
    if not employee_number or not employee_number.strip():
        return jsonify({
            "status": "error",
            "message": "请提供员工编号"
        }), 400

    # 检查文件上传
    if 'excel' not in request.files:
        return jsonify({"status": "error", "message": "未上传文件"}), 400

    file = request.files['excel']
    if file.filename == '':
        return jsonify({"status": "error", "message": "文件名为空"}), 400

    # 检查文件类型
    if not allowed_file(file.filename):
        return jsonify({"status": "error", "message": "仅支持.xlsx或.xls文件"}), 400

    try:
        # 创建上传目录
        os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

        # 保存文件
        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(file_path)

        # 解析Excel
        df = pd.read_excel(file_path)  # 从保存的文件读取

        # 验证列名
        required_columns = {'姓名', '手机号', '到期日期', '存款金额'}
        if not required_columns.issubset(df.columns):
            missing = required_columns - set(df.columns)
            return jsonify({
                "status": "error",
                "message": f"缺少必要列：{', '.join(missing)}"
            }), 400

        # 处理数据
        records = []
        for _, row in df.iterrows():
            records.append(Customer(
                employee_number=employee_number[:20],  # 添加员工号，限制长度防止SQL注入
                name=str(row['姓名']),
                phone=str(row['手机号']),
                expire_date=pd.to_datetime(row['到期日期']).date(),
                amount=float(row['存款金额']),
                notified=False  # 显式设置默认值
            ))

        # 批量插入
        db.session.bulk_save_objects(records)
        db.session.commit()

        return jsonify({
            "status": "success",
            "count": len(df),
            "message": f"成功插入 {len(df)} 条记录"
        })

    except pd.errors.ParserError as e:
        return jsonify({
            "status": "error",
            "message": "文件解析失败，请检查Excel格式"
        }), 400

    except ValueError as e:
        db.session.rollback()
        return jsonify({
            "status": "error",
            "message": f"数据格式错误：{str(e)}"
        }), 400

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"上传失败：{str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": f"服务器内部错误：{str(e)}"
        }), 500


# 新增Employee数据模型
class Employee(db.Model):
    __tablename__ = 'employees'
    employee_id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 自增主键
    employee_number = db.Column(db.String(20), unique=True, nullable=False)  # 必填且唯一
    name = db.Column(db.String(100), nullable=False)  # 必填
    password = db.Column(db.String(255), nullable=False)  # 必填
    department_id = db.Column(db.Integer)  # 可选
    job_title = db.Column(db.String(100))  # 可选
    hire_date = db.Column(db.Date, nullable=False)  # 必填
    email = db.Column(db.String(100), unique=True, nullable=True, default=None)  # 明确允许 NULL 可选但唯一
    organization_code = db.Column(db.String(9), nullable=True, comment='所在机构代码（9位数字）')


# 新增注册接口（添加到现有路由下方）
@app.route('/api/register', methods=['POST'])
def register_employee():
    try:
        data = request.get_json()

        # --- 输入验证阶段 ---
        # 必填字段校验
        required_fields = ['employee_number', 'name', 'password']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少必要字段"}), 400

        # 密码复杂度校验（至少8位，包含字母和数字）
        if len(data['password']) < 8 or \
                not re.search(r"[A-Za-z]", data['password']) or \
                not re.search(r"\d", data['password']):
            return jsonify({"status": "error", "message": "密码需至少8位且包含字母和数字"}), 400

        # --- 唯一性检查阶段 ---
        # 检查员工编号是否重复
        if Employee.query.filter_by(employee_number=data['employee_number']).first():
            return jsonify({"status": "error", "message": "员工编号已存在"}), 409

        # --- 邮箱处理逻辑 ---
        email = data.get('email')

        # 情况 1：字段不存在或值为 null
        if email is None:
            email = None
        # 情况 2：字段存在但为空字符串
        elif isinstance(email, str):
            email = email.strip()  # 去除首尾空格
            email = email if email else None  # 空字符串转为 None

        # --- 邮箱唯一性检查（仅当 email 非空时）---
        if email:
            # 格式校验（正则加强版）
            if not re.match(r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", email):
                return jsonify({"status": "error", "message": "邮箱格式无效"}), 400

            # 不区分大小写的唯一性检查
            existing = Employee.query.filter(
                func.lower(Employee.email) == func.lower(email)
            ).first()
            if existing:
                return jsonify({"status": "error", "message": "邮箱已被注册"}), 409

        # --- 数据处理阶段 ---
        # 密码哈希处理（自动包含盐）
        raw_password = data['password']
        hashed_password = bcrypt.hashpw(raw_password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")

        # 覆盖明文密码内存（增强安全性）
        data['password'] = "overwritten_for_safety"
        del raw_password  # 主动删除引用

        # 日期解析
        # hire_date = datetime.strptime(data['hire_date'], '%Y-%m-%d').date()
        # 日期解析
        if data.get('hire_date'):
            hire_date = datetime.strptime(data['hire_date'], '%Y-%m-%d').date()
        else:
            # 使用当前中国时区的日期
            hire_date = datetime.now(china_tz).date()

        # --- 数据入库阶段 ---
        new_employee = Employee(
            employee_number=data['employee_number'],
            name=data['name'],
            password=hashed_password,
            hire_date=hire_date,
            job_title=data.get('job_title'),
            department_id=data.get('department_id'),
            email=email
        )

        db.session.add(new_employee)
        db.session.commit()

        # ============== Token生成新增开始 ==============
        # 生成JWT访问令牌（使用员工ID作为身份标识）
        # 旧的token access_token = create_access_token(identity=new_employee.employee_id)
        access_token = create_access_token(identity=str(new_employee.employee_id))

        # 构建响应数据时添加token字段
        response_data = {
            "status": "success",
            "message": "注册成功",
            "data": {
                "employee_id": new_employee.employee_id,
                "employee_number": new_employee.employee_number,
                "name": new_employee.name,
                "token": access_token  # 新增token字段
            }
        }
        # ============== Token生成新增结束 ==============

        return jsonify(response_data), 201

    except ValueError as e:
        # 日期解析错误专属捕获
        return jsonify({"status": "error", "message": "日期格式错误，请使用YYYY-MM-DD格式"}), 400
    except IntegrityError as e:  # 捕获 SQLAlchemy 的 IntegrityError
        db.session.rollback()
        error_message = str(e.orig)
        if "Duplicate entry" in error_message:
            if "employee_number" in error_message:
                return jsonify({"status": "error", "message": "员工编号已存在"}), 409
            elif "email" in error_message:
                return jsonify({"status": "error", "message": "邮箱已被注册"}), 409
        return jsonify({"status": "error", "message": "数据冲突"}), 409
    except Exception as e:
        # 全局异常捕获（最后防线）
        db.session.rollback()
        app.logger.error(f"注册接口异常: {str(e)}")  # 记录日志但不要暴露敏感信息
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500


# ============== 登录接口新增开始 ==============
@app.route('/api/login', methods=['POST'])
def employee_login():
    try:
        data = request.get_json()

        # 必填字段校验
        required_fields = ['employee_number', 'name', 'password']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "员工编号、姓名和密码必须填写"}), 400

        # 根据员工编号查询用户
        employee = Employee.query.filter_by(employee_number=data['employee_number']).first()

        # 用户不存在情况
        if not employee:
            return jsonify({"status": "error", "message": "员工不存在"}), 404

        # 验证姓名一致性（严格匹配）
        if employee.name != data['name']:
            return jsonify({"status": "error", "message": "姓名与记录不符"}), 401

        # 验证密码正确性
        if not bcrypt.checkpw(data['password'].encode('utf-8'), employee.password.encode('utf-8')):
            return jsonify({"status": "error", "message": "密码错误"}), 401

        # 生成永久有效的Token（移除时效性）
        access_token = create_access_token(
            # 旧方法 identity=employee.employee_id,
            identity=str(employee.employee_id),  # 转为字符串
            expires_delta=None  # 设置为永不过期
        )

        # 构建响应数据
        response_data = {
            "status": "success",
            "message": "登录成功",
            "data": {
                "employee_id": employee.employee_id,
                "employee_number": employee.employee_number,
                "name": employee.name,
                "token": access_token
            }
        }

        return jsonify(response_data), 200

    except Exception as e:
        app.logger.error(f"登录接口异常: {str(e)}")
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500


# ============== 登录接口新增结束 ==============

# ============== 员工信息管理接口（整合GET/PATCH）开始 ==============
@app.route('/api/employee/update', methods=['GET', 'PATCH'])  # 同时支持GET和PATCH
@jwt_required()
def employee_info_management():
    try:
        # 公共逻辑：获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)

        if not employee:
            return jsonify({
                "status": "error",
                "message": "用户不存在"
            }), 404

        # ============== GET 请求处理（新增）==============
        if request.method == 'GET':
            return jsonify({
                "status": "success",
                "data": {
                    "employee_id": employee.employee_id,
                    "employee_number": employee.employee_number,
                    "name": employee.name,
                    "department_id": employee.department_id,
                    "job_title": employee.job_title,
                    "organization_code": employee.organization_code,
                    "email": employee.email,
                    "hire_date": employee.hire_date.strftime('%Y-%m-%d')
                }
            }), 200

        # ============== PATCH 请求处理（原逻辑）==============
        if request.method == 'PATCH':
            data = request.get_json()

            # 处理姓名更新
            if 'name' in data:
                new_name = data['name'].strip()
                if not new_name:
                    return jsonify({
                        "status": "error",
                        "message": "姓名不能为空"
                    }), 400
                employee.name = new_name

            # 处理密码更新
            if 'password' in data:
                new_password = data['password'].strip()  # 去除首尾空格

                # 只有在新密码非空时进行校验和更新
                if new_password:  # ✅ 新增空值判断
                    # 复杂度校验
                    if len(new_password) < 8 or \
                            not re.search(r"[A-Za-z]", new_password) or \
                            not re.search(r"\d", new_password):
                        return jsonify({
                            "status": "error",
                            "message": "密码需至少8位且包含字母和数字"
                        }), 400

                    # 哈希处理
                    hashed_pw = bcrypt.hashpw(new_password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
                    employee.password = hashed_pw
                else:
                    # 密码字段存在但为空时，跳过更新（保持原密码）
                    pass

            # 处理岗位更新
            if 'job_title' in data:
                new_job_title = data['job_title'].strip() if data['job_title'] else None
                if new_job_title and len(new_job_title) > 100:
                    return jsonify({
                        "status": "error",
                        "message": "岗位名称超过长度限制"
                    }), 400
                employee.job_title = new_job_title

            # 处理机构代码更新（9位数字校验）
            if 'organization_code' in data:
                org_code = data['organization_code']
                if org_code in [None, "", "null"]:
                    employee.organization_code = None
                else:
                    org_code = str(org_code).strip()
                    if not re.fullmatch(r'^\d{9}$', org_code):
                        return jsonify({
                            "status": "error",
                            "message": "机构代码必须为9位数字"
                        }), 400
                    employee.organization_code = org_code

            db.session.commit()

            return jsonify({
                "status": "success",
                "message": "信息更新成功",
                "data": {
                    "employee_number": employee.employee_number,
                    "updated_fields": list(data.keys())
                }
            }), 200

    except ValueError as e:
        return jsonify({
            "status": "error",
            "message": "参数类型错误"
        }), 400
    except IntegrityError as e:
        db.session.rollback()
        app.logger.error(f"数据库完整性错误: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "数据冲突，请检查唯一性约束"
        }), 409
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"接口异常: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# ============== 员工信息管理接口（整合GET/PATCH）结束 ==============

# ============== 数据预览接口新增开始 ==============

@app.route('/api/customers', methods=['GET'])
def get_customers():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')  # get传值与post的区别要注意
        # employee_number = request.form.get('employee_number')  # 从form-data获取

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库
        customers = Customer.query.filter_by(
            employee_number=employee_number
        ).order_by(
            Customer.expire_date.desc()
        ).all()

        # 格式化结果
        result = []
        for customer in customers:
            result.append({
                "name": customer.name,
                "phone": customer.phone,
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount)  # 转换为float类型便于序列化
            })

        return jsonify({
            "status": "success",
            "count": len(result),
            "data": result
        }), 200

    except Exception as e:
        app.logger.error(f"查询客户数据失败: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# ============== 数据预览接口新增结束 ==============

# ============== 到期提醒预览接口新增开始 ==============
@app.route('/api/pending_sms_customers', methods=['GET'])
def get_pending_sms_customers():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库（新增sms_reminded过滤条件）
        customers = Customer.query.filter_by(
            employee_number=employee_number,
            sms_reminded=0  # 新增过滤条件
        ).order_by(
            Customer.expire_date.asc()  # 按到期日期升序排列（即将到期的在前）
        ).all()

        # 格式化结果
        result = []
        for customer in customers:
            result.append({
                "id": customer.id,  # 新增返回客户ID便于后续操作
                "name": customer.name,
                "phone": customer.phone,
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount),
                "sms_reminded": customer.sms_reminded  # 返回当前状态
            })

        return jsonify({
            "status": "success",
            "count": len(result),
            "data": result
        }), 200

    except Exception as e:
        app.logger.error(f"查询待提醒客户失败: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# ============== 到期提醒预览接口新增结束 ==============

# ============== 到期提醒回调更新sms_reminded接口新增开始 ==============
@app.route('/api/update_sms_status', methods=['POST'])
def update_sms_status():
    try:
        data = request.get_json()

        # 必填字段校验
        required_fields = ['customer_id', 'employee_number']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少客户ID或员工编号"}), 400

        # 查询客户记录（确保员工号匹配）
        customer = Customer.query.filter_by(
            id=data['customer_id'],
            employee_number=data['employee_number']
        ).first()

        if not customer:
            return jsonify({"status": "error", "message": "未找到匹配的客户记录或权限不足"}), 404

        # 更新短信提醒状态
        customer.sms_reminded = 1
        db.session.commit()

        return jsonify({
            "status": "success",
            "message": "短信状态更新成功",
            "data": {
                "customer_id": customer.id,
                "sms_reminded": customer.sms_reminded
            }
        }), 200

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"更新短信状态失败: {str(e)}", exc_info=True)
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500


# ============== 到期提醒回调更新sms_reminded接口新增结束 ==============

# ============== 客户金额汇总接口新增开始 ==============
@app.route('/api/client_summary', methods=['GET'])
def get_client_summary():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库并聚合数据
        results = db.session.query(
            Customer.name,
            Customer.phone,
            func.sum(Customer.amount).label('total_amount')
        ).filter_by(
            employee_number=employee_number
        ).group_by(
            Customer.name,
            Customer.phone
        ).order_by(
            func.sum(Customer.amount).desc()
        ).all()

        # 格式化结果
        summary_list = [{
            "name": name,
            "phone": phone,
            "total_amount": float(total_amount)
        } for name, phone, total_amount in results]

        # 构建响应数据（包含排名和完整列表）
        response_data = {
            "status": "success",
            "data": {
                # 前3名作为排行榜
                "ranking": summary_list[:3],
                # 完整列表（已排序）
                "full_list": summary_list
            }
        }

        return jsonify(response_data), 200

    except Exception as e:
        app.logger.error(f"获取客户汇总失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# ============== 客户金额汇总接口新增结束 ==============

# ============== 搜索客户信息接口新增开始 ==============

@app.route('/api/search_customers', methods=['GET'])
def search_customers():
    try:
        # 获取查询参数
        name = request.args.get('name')
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not name or not employee_number:
            return jsonify({
                "status": "error",
                "message": "姓名和员工编号为必填参数"
            }), 400

        # 查询数据库（模糊匹配姓名）
        customers = Customer.query.filter(
            Customer.employee_number == employee_number,
            Customer.name.like(f"%{name}%")
        ).order_by(
            Customer.expire_date.desc()
        ).all()

        # 按客户分组汇总数据
        result_dict = {}
        for customer in customers:
            key = (customer.name, customer.phone)
            if key not in result_dict:
                result_dict[key] = {
                    "name": customer.name,
                    "phone": customer.phone,
                    "total_amount": 0.0,
                    "deposits": []
                }

            deposit = {
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount)
            }
            result_dict[key]["deposits"].append(deposit)
            result_dict[key]["total_amount"] += float(customer.amount)

        result_list = []
        for item in result_dict.values():
            item["total_amount"] = round(item["total_amount"], 2)
            item["deposits"].sort(key=lambda x: x["expire_date"], reverse=True)
            result_list.append(item)

        # ============== 新增消息提示逻辑 ==============
        if len(result_list) == 0:
            return jsonify({
                "status": "success",
                "message": "此客户记录不存在",
                "count": 0,
                "data": []
            }), 200
        else:
            return jsonify({
                "status": "success",
                "message": f"找到 {len(result_list)} 条客户记录",
                "count": len(result_list),
                "data": result_list
            }), 200
        # ============== 新增逻辑结束 ==============

    except Exception as e:
        app.logger.error(f"客户搜索失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# ============== 搜索客户信息接口新增结束 ==============

# ============== 重要提醒接口新增开始 =================
# 新增提醒接口模型
class ImportantAlert(db.Model):
    __tablename__ = 'important_alerts'
    alert_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    employee_number = db.Column(db.String(20), db.ForeignKey('employees.employee_number'), nullable=False)
    alert_content = db.Column(db.Text, nullable=False)
    reminder_date = db.Column(db.DateTime, nullable=False)
    submitted_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    is_successful = db.Column(db.Boolean, default=False)
    device_info = db.Column(db.String(255))


@app.route('/api/create_alert', methods=['POST'])
def create_alert():
    try:
        # 从请求中获取柜员号
        data = request.get_json()
        employee_number = data.get('employee_number')

        # 验证柜员号是否存在
        if not employee_number:
            return jsonify({"status": "error", "message": "缺少柜员号"}), 400

        # 查询员工有效性
        employee = Employee.query.filter_by(employee_number=employee_number).first()
        if not employee:
            return jsonify({"status": "error", "message": "无效的柜员号"}), 404

        # 校验其他必填字段
        required_fields = ['alert_content', 'reminder_date']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少提醒内容或提醒时间"}), 400

        # 日期格式处理
        try:
            reminder_date = datetime.strptime(data['reminder_date'], '%Y-%m-%d %H:%M:%S')
        except ValueError:
            return jsonify({"status": "error", "message": "时间格式应为YYYY-MM-DD HH:MM:SS"}), 400

        # 创建提醒记录
        new_alert = ImportantAlert(
            employee_number=employee_number,
            alert_content=data['alert_content'],
            reminder_date=reminder_date,
            device_info=data.get('device_info', 'unknown')  # 可选设备信息
        )

        db.session.add(new_alert)
        db.session.commit()

        return jsonify({
            "status": "success",
            "message": "提醒创建成功",
            "data": {
                "alert_id": new_alert.alert_id,
                "reminder_time": new_alert.reminder_date.strftime('%Y-%m-%d %H:%M')
            }
        }), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"创建提醒失败: {str(e)}", exc_info=True)
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500


# ============== 重要提醒接口新增结束 =================


# 在Employee模型下方添加新数据模型
class AnonymousFeedback(db.Model):
    __tablename__ = 'anonymous_feedback'
    id = db.Column(db.Integer, primary_key=True)
    employee_number = db.Column(db.String(20), nullable=False)
    content = db.Column(db.String(500), nullable=False)
    images = db.Column(db.JSON)  # 使用SQLAlchemy的JSON类型
    like_count = db.Column(db.Integer, default=0)
    create_time = db.Column(db.DateTime, default=datetime.now)
    is_deleted = db.Column(db.Boolean, default=False)


class FeedbackLike(db.Model):
    __tablename__ = 'feedback_likes'
    id = db.Column(db.Integer, primary_key=True)
    feedback_id = db.Column(db.Integer, db.ForeignKey('anonymous_feedback.id'), nullable=False)
    employee_number = db.Column(db.String(20), nullable=False)
    create_time = db.Column(db.DateTime, default=datetime.now)


def allowed_file(filename):
    """修正后的文件类型验证方法"""
    allowed_extensions = app.config.get(
        'ALLOWED_IMAGE_EXTENSIONS',
        {'png', 'jpg', 'jpeg', 'gif', 'webp'}  # 默认值
    )
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in allowed_extensions


# 在现有路由后添加新接口
# ============== 匿名吐槽接口开始 ==============
# 图片上传接口（图床）
@app.route('/api/upload/image', methods=['POST'])
@jwt_required()
def upload_image():
    try:
        # ================= 用户验证 =================
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # ================= 文件接收 =================
        if 'image' not in request.files:
            return jsonify({"status": "error", "message": "请选择要上传的图片"}), 400

        uploaded_files = request.files.getlist('image')
        if len(uploaded_files) > 1:
            return jsonify({"status": "error", "message": "单次最多上传一张图片"}), 400

        uploaded_urls = []
        for file in uploaded_files:
            # ============= 文件验证 =============
            # 验证文件类型
            if not allowed_file(file.filename):
                return jsonify({
                    "status": "error",
                    "message": f"不支持的文件类型: {file.filename.split('.')[-1]} (支持类型: {', '.join(app.config['ALLOWED_IMAGE_EXTENSIONS'])})"
                }), 400

            # 验证文件大小 (配置读取)
            max_size = app.config.get('MAX_IMAGE_SIZE', 2 * 1024 * 1024)
            file.seek(0, os.SEEK_END)
            file_length = file.tell()
            if file_length > max_size:
                return jsonify({
                    "status": "error",
                    "message": f"图片大小超过{max_size // 1024 // 1024}MB限制"
                }), 413
            file.seek(0)  # 重置文件指针

            # ============= 图床请求 =============
            try:
                # 构建符合EasyImages要求的参数
                upload_data = {
                    'token': app.config['EASYIMAGES_TOKEN'],
                    'api': 'upload'  # 关键参数：指定上传动作
                }
                upload_files = {
                    'image': (file.filename, file.stream, file.mimetype)
                }

                # 发送请求
                response = requests.post(
                    'https://EasyImages.10v.cc/api/index.php',
                    files=upload_files,
                    data=upload_data,
                    timeout=15
                )

                # ========= 响应处理 =========
                # 记录原始响应
                app.logger.debug(f"图床响应状态码: {response.status_code}")
                app.logger.debug(f"图床原始响应: {response.text}")

                # 关键调整1：优先检查JSON结构
                try:
                    result = response.json()
                except json.JSONDecodeError:
                    return jsonify({
                        "status": "error",
                        "message": "图床返回数据格式异常"
                    }), 502

                # 关键调整2：根据实际响应字段判断
                if result.get('code') == 200:  # 成功状态
                    # 关键调整3：多种URL字段尝试
                    image_url = (
                            result.get('data', {}).get('url') or  # 文档标准格式
                            result.get('url') or  # 备用字段1
                            result.get('image_url')  # 备用字段2
                    )

                    if image_url:
                        uploaded_urls.append(image_url)
                    else:
                        app.logger.error("图床URL字段缺失")
                        continue
                else:
                    error_msg = result.get('msg') or result.get('message') or '未知错误'
                    app.logger.error(f"图床错误: {error_msg}")
                    return jsonify({
                        "status": "error",
                        "message": f"图床返回错误: {error_msg} (代码: {result.get('code')})"
                    }), 502

            except requests.exceptions.RequestException as e:
                app.logger.error(f"图床连接异常: {str(e)}")
                return jsonify({
                    "status": "error",
                    "message": "图片服务暂时不可用"
                }), 503

        # ============ 返回结果 ============
        if not uploaded_urls:
            return jsonify({
                "status": "error",
                "message": "所有图片上传失败"
            }), 400

        return jsonify({
            "status": "success",
            "data": {
                "urls": uploaded_urls
            }
        }), 200

    except Exception as e:
        app.logger.error(f"上传处理异常: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# 发布吐槽按钮（文字图床上传接口）

def validate_image_url(url):
    """严格验证图片URL合法性"""
    try:
        parsed = urlparse(url)

        # ============= 预处理小写 =============
        # 转换域名和路径为小写进行验证（不改变原始URL）
        lower_netloc = parsed.netloc.lower()
        lower_path = parsed.path.lower()

        # ============= 配置获取 =============
        valid_domains = app.config.get(
            'VALID_IMAGE_DOMAINS',
            ['easyimages.10v.cc', 'your-cdn-domain.com']
        )
        # 配置域名统一小写处理
        lower_valid_domains = [d.lower() for d in valid_domains]

        # ============= 路径正则验证 =============
        # 使用小写路径进行匹配（根据实际图床路径结构调整正则）
        path_pattern = r'^/i/\d{4}/\d{2}/\d{2}/[a-z0-9_-]+\.(jpg|png|gif|webp)$'

        # ============= 综合验证条件 =============
        return (
                lower_netloc in lower_valid_domains and  # 域名校验（小写）
                parsed.scheme in ['http', 'https'] and  # 协议校验
                re.fullmatch(path_pattern, lower_path) and  # 路径校验（小写）
                bool(parsed.query) is False  # 不允许带查询参数
        )

    except Exception as e:
        app.logger.error(f"URL验证异常: {str(e)}")
        return False


@app.route('/api/feedback', methods=['POST'])
@jwt_required()
def create_feedback():
    try:
        # ============== 用户身份验证 ==============
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            app.logger.warning(f"用户不存在: ID={current_user_id}")
            return jsonify({
                "status": "error",
                "code": "USER_NOT_FOUND",
                "message": "用户不存在"
            }), 404

        # ============== 数据完整性校验 ==============
        data = request.get_json()
        if not data:
            return jsonify({
                "status": "error",
                "code": "EMPTY_REQUEST",
                "message": "请求体不能为空"
            }), 400

        # 内容验证（允许空内容但不超过500字）
        content = data.get('content', '')
        if len(content) > 500:
            app.logger.info(f"内容过长: {len(content)}字符")
            return jsonify({
                "status": "error",
                "code": "CONTENT_TOO_LONG",
                "message": "内容超过500字限制"
            }), 400

        # 图片URL处理（字段名保持与上传接口一致）
        image_urls = data.get('urls', [])  # 注意字段名改为 urrs 与上传接口一致

        # 类型检查
        if not isinstance(image_urls, list):
            return jsonify({
                "status": "error",
                "code": "INVALID_IMAGE_URLS",
                "message": "图片参数格式错误"
            }), 400

        # 数量限制（根据需求调整）
        MAX_IMAGES = 1
        if len(image_urls) > MAX_IMAGES:
            return jsonify({
                "status": "error",
                "code": "TOO_MANY_IMAGES",
                "message": f"最多上传{MAX_IMAGES}张图片"
            }), 400

        # ============== 安全验证 ==============
        invalid_urls = [url for url in image_urls if not validate_image_url(url)]
        if invalid_urls:
            app.logger.warning(f"非法图片地址: {invalid_urls}")
            return jsonify({
                "status": "error",
                "code": "INVALID_IMAGE_URL",
                "message": f"检测到{len(invalid_urls)}个非法图片地址",
                "data": {
                    "invalid_urls": invalid_urls[:3]  # 防止泄露过多信息
                }
            }), 400

        # ============== 数据库操作 ==============
        try:
            new_feedback = AnonymousFeedback(
                employee_number=employee.employee_number,
                content=content.strip(),  # 去除首尾空白
                images=image_urls or None  # 空数组存为NULL
            )

            db.session.add(new_feedback)
            db.session.commit()

            app.logger.info(
                f"发布成功: 用户{employee.employee_number}, "
                f"内容长度{len(content)}, 图片数{len(image_urls)}"
            )

            return jsonify({
                "status": "success",
                "data": {
                    "id": new_feedback.id,
                    "create_time": new_feedback.create_time.isoformat(),
                    "image_count": len(image_urls)
                }
            }), 201

        except IntegrityError as e:
            db.session.rollback()
            app.logger.error(f"数据完整性错误: {str(e)}")
            return jsonify({
                "status": "error",
                "code": "DB_INTEGRITY_ERROR",
                "message": "数据存储失败"
            }), 500

    except json.JSONDecodeError:
        return jsonify({
            "status": "error",
            "code": "INVALID_JSON",
            "message": "非法的JSON格式"
        }), 400

    except Exception as e:
        db.session.rollback()
        app.logger.error(
            f"发布失败: {str(e)}\n"
            f"用户: {current_user_id}\n"
            f"内容: {content[:100]}...\n"
            f"图片: {image_urls}"
        )
        return jsonify({
            "status": "error",
            "code": "SERVER_ERROR",
            "message": "服务暂时不可用"
        }), 500


# 获取吐槽列表
@app.route('/api/feedbacks', methods=['GET'])
@jwt_required()
def get_feedbacks():
    try:

        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数

        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        page = request.args.get('page', 1, type=int)
        size = request.args.get('size', 10, type=int)

        # 获取轮播图（示例数据）
        banners = ["https://example.com/banner1.jpg", "https://example.com/banner2.jpg"]

        # 查询吐槽
        query = AnonymousFeedback.query.filter_by(is_deleted=False)
        pagination = query.order_by(AnonymousFeedback.create_time.desc()).paginate(
            page=page, per_page=size, error_out=False)

        feedbacks = []
        for item in pagination.items:
            # 检查是否是自己发布的
            is_mine = item.employee_number == current_employee_number

            # 检查是否已点赞
            is_liked = FeedbackLike.query.filter_by(
                feedback_id=item.id,
                employee_number=current_employee_number
            ).first() is not None

            feedbacks.append({
                "id": item.id,
                "content": item.content,
                "images": item.images,
                "like_count": item.like_count,
                "create_time": item.create_time.isoformat(),
                "is_mine": is_mine,
                "is_liked": is_liked
            })

        return jsonify({
            "status": "success",
            "data": {
                "banners": banners,
                "feedbacks": feedbacks,
                "pagination": {
                    "page": page,
                    "size": size,
                    "total": pagination.total
                }
            }
        })

    except Exception as e:
        app.logger.error(f"获取列表失败: {str(e)}")
        return jsonify({"status": "error", "message": "获取失败"}), 500


# 删除吐槽
@app.route('/api/feedback/<int:feedback_id>', methods=['DELETE'])
@jwt_required()
def delete_feedback(feedback_id):
    try:
        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数

        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        feedback = AnonymousFeedback.query.get(feedback_id)

        if not feedback or feedback.is_deleted:
            return jsonify({"status": "error", "message": "内容不存在"}), 404

        if feedback.employee_number != current_employee_number:
            # app.logger.debug(f"目标反馈用户: {feedback.employee_number}")
            return jsonify({"status": "error", "message": "无操作权限"}), 403

        feedback.is_deleted = True
        db.session.commit()

        return jsonify({"status": "success"})

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"删除失败: {str(e)}")
        return jsonify({"status": "error", "message": "删除失败"}), 500


# 点赞操作
@app.route('/api/feedback/<int:feedback_id>/like', methods=['POST'])
@jwt_required()
def toggle_like(feedback_id):
    try:
        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数

        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        action = request.json.get('action', 'add')

        feedback = AnonymousFeedback.query.get(feedback_id)
        if not feedback or feedback.is_deleted:
            return jsonify({"status": "error", "message": "内容不存在"}), 404

        existing_like = FeedbackLike.query.filter_by(
            feedback_id=feedback_id,
            employee_number=current_employee_number
        ).first()

        if action == 'add':
            if existing_like:
                return jsonify({"status": "error", "message": "已点赞"}), 400

            new_like = FeedbackLike(
                feedback_id=feedback_id,
                employee_number=current_employee_number
            )
            feedback.like_count += 1
            db.session.add(new_like)

        elif action == 'remove':
            if not existing_like:
                return jsonify({"status": "error", "message": "未点赞"}), 400

            feedback.like_count -= 1
            db.session.delete(existing_like)

        db.session.commit()
        return jsonify({
            "status": "success",
            "like_count": feedback.like_count
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"点赞操作失败: {str(e)}")
        return jsonify({"status": "error", "message": "操作失败"}), 500


# 轮播图片
# 轮播图片模型定义
class Banner(db.Model):
    __tablename__ = 'banners'
    id = db.Column(db.Integer, primary_key=True)
    image_url = db.Column(db.String(255), nullable=False)  # 图片地址
    sort_order = db.Column(db.Integer, default=0)  # 排序权重
    is_active = db.Column(db.Boolean, default=True)  # 是否启用
    create_time = db.Column(db.DateTime, default=datetime.now)


@app.route('/api/banners', methods=['GET'])
def get_banners():
    try:
        # 查询启用的轮播图并按排序权重降序
        banners = Banner.query.filter_by(is_active=True) \
            .order_by(Banner.sort_order.desc()) \
            .all()

        # 提取图片URL
        banner_urls = [banner.image_url for banner in banners]

        return jsonify({
            "status": "success",
            "data": {
                "banners": banner_urls
            }
        })

    except Exception as e:
        app.logger.error(f"获取轮播图失败: {str(e)}")
        return jsonify({"status": "error", "message": "服务异常"}), 500


# ============== 匿名吐槽接口结束 ==============

# ============== 业绩模板接口开始 ==============

# 创建东八区时区对象
china_tz = pytz.timezone('Asia/Shanghai')

def get_china_time():
    """获取当前中国时间（带时区）"""
    return datetime.now(china_tz)

def to_china_time(dt):
    """将时间转换为中国时区时间"""
    if dt.tzinfo is None:
        # 如果是无时区时间，假定为UTC
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(china_tz)

def format_china_time(dt):
    """
    格式化时间为中国时区字符串
    处理各种输入情况：
    - 带时区的时间对象
    - 无时区的时间对象（假设为UTC）
    - 字符串时间（转换为对象后再处理）
    """
    if dt is None:
        return None

    # 如果是字符串，尝试转换为datetime对象
    if isinstance(dt, str):
        try:
            # 尝试解析ISO格式
            dt = datetime.fromisoformat(dt)
        except ValueError:
            try:
                # 尝试解析标准格式
                dt = datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
            except ValueError:
                return dt  # 无法解析，返回原始字符串

    # 处理 naive datetime (无时区)
    if dt.tzinfo is None:
        # 假定是 UTC 时间，转换为中国时区
        dt = dt.replace(tzinfo=timezone.utc)

    # 转换为中国时区并格式化
    return dt.astimezone(china_tz).strftime('%Y-%m-%d %H:%M:%S')

# 格式化时间函数（确保UTC时间和毫秒精度）
def format_datetime(dt):
    if dt is None:
        return None
    return dt.astimezone(timezone.utc).strftime('%Y-%m-%dT%H:%M:%S.%f')[:-3] + 'Z'

# 业绩模板模型（修复时间默认值）
class PerformanceTemplate(db.Model):
    __tablename__ = 'performance_templates'
    template_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    emp_number = db.Column(db.String(20), db.ForeignKey('employees.employee_number'), nullable=False)
    template_name = db.Column(db.String(100), nullable=False)
    template_data = db.Column(db.JSON, nullable=False)
    is_default = db.Column(db.Boolean, default=False)
    # created_at = db.Column(db.DateTime, default=lambda: get_china_time())
    # updated_at = db.Column(db.DateTime, default=lambda: get_china_time(), onupdate=lambda: get_china_time())
    # 直接使用UTC时间存储，避免时区转换问题
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


# 业绩记录模型（修复时间默认值）
class PerformanceRecord(db.Model):
    __tablename__ = 'performance_records'
    record_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    template_id = db.Column(db.Integer, db.ForeignKey('performance_templates.template_id'), nullable=False)
    emp_number = db.Column(db.String(20), db.ForeignKey('employees.employee_number'), nullable=False)
    values_data = db.Column(db.JSON, nullable=False)
    generated_text = db.Column(db.Text, nullable=False)
    # created_at = db.Column(db.DateTime, default=lambda: get_china_time())
    # record_date = db.Column(db.Date, default=lambda: get_china_time().date())
    # 使用带时区的时间对象
    created_at = db.Column(db.DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
    record_date = db.Column(db.Date, default=lambda: datetime.now(timezone.utc).date())


# 创建新模板 - 修复时间问题并添加详细日志
# 创建新模板（完整修复时间问题）
@app.route('/api/performance_templates', methods=['POST'])
@jwt_required()
def create_performance_template():
    try:
        # 记录请求开始时间
        start_time = datetime.now(timezone.utc)
        app.logger.info(f"[创建模板] 请求开始时间: {format_datetime(start_time)}")

        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            app.logger.warning(f"[创建模板] 用户不存在: user_id={current_user_id}")
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        app.logger.info(f"[创建模板] 当前用户: {employee.employee_number}")

        data = request.get_json()
        app.logger.info(f"[创建模板] 请求数据: {json.dumps(data, ensure_ascii=False)}")

        # 验证必填字段
        required_fields = ['template_name', 'template_data']
        if not all(field in data for field in required_fields):
            missing = [field for field in required_fields if field not in data]
            app.logger.warning(f"[创建模板] 缺少必填字段: {missing}")
            return jsonify({
                "status": "error",
                "message": "缺少模板名称或模板数据",
                "missing_fields": missing
            }), 400

        # 验证模板数据结构
        template_data = data['template_data']
        if not isinstance(template_data, dict) or 'items' not in template_data:
            app.logger.warning(f"[创建模板] 模板数据结构无效: {type(template_data)}")
            return jsonify({
                "status": "error",
                "message": "模板数据结构无效"
            }), 400

        # 验证模板项
        items = template_data['items']
        if not isinstance(items, list) or len(items) == 0:
            app.logger.warning(f"[创建模板] 模板缺少条目: items={items}")
            return jsonify({
                "status": "error",
                "message": "模板必须包含至少一个条目"
            }), 400

        for index, item in enumerate(items):
            if 'product_name' not in item or 'unit' not in item:
                app.logger.warning(f"[创建模板] 条目缺少必要字段: index={index}, item={item}")
                return jsonify({
                    "status": "error",
                    "message": "每个模板项必须包含产品名称和单位"
                }), 400

        # 创建新模板
        new_template = PerformanceTemplate(
            emp_number=employee.employee_number,
            template_name=data['template_name'],
            template_data=template_data,
            is_default=data.get('is_default', False)
        )

        # 添加前记录时间
        if new_template.created_at:
            app.logger.info(f"[创建模板] ORM对象创建时间: {format_datetime(new_template.created_at)}")
        else:
            # 手动设置时间为后备方案
            manual_time = datetime.now(timezone.utc)
            new_template.created_at = manual_time
            app.logger.warning(f"[创建模板] 手动设置ORM时间: {format_datetime(manual_time)}")

        db.session.add(new_template)

        # 提交前记录时间
        before_commit = datetime.now(timezone.utc)
        app.logger.info(f"[创建模板] 提交前时间: {format_datetime(before_commit)}")

        db.session.commit()

        # 提交后记录时间
        after_commit = datetime.now(timezone.utc)
        app.logger.info(f"[创建模板] 提交后时间: {format_datetime(after_commit)}")

        # 直接使用ORM对象时间（避免查询延迟）
        db_time = format_datetime(new_template.created_at)
        app.logger.info(f"[创建模板] 数据库实际创建时间: {db_time}")

        return jsonify({
            "status": "success",
            "message": "模板创建成功",
            "data": {
                "template_id": new_template.template_id,
                "template_name": new_template.template_name,
                "created_at": format_china_time(new_template.created_at)
            }
        }), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"[创建模板] 失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误",
            "error_details": str(e)
        }), 500

# 获取用户所有模板
@app.route('/api/performance_templates', methods=['GET'])
@jwt_required()
def get_performance_templates():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 查询用户的所有模板
        templates = PerformanceTemplate.query.filter_by(
            emp_number=employee.employee_number
        ).order_by(
            PerformanceTemplate.is_default.desc(),
            PerformanceTemplate.updated_at.desc()
        ).all()

        # 格式化结果
        templates_data = []
        for template in templates:
            templates_data.append({
                "template_id": template.template_id,
                "template_name": template.template_name,
                "is_default": template.is_default,
                "item_count": len(template.template_data.get('items', [])),
                "created_at": template.created_at.strftime('%Y-%m-%d'),
                "updated_at": template.updated_at.strftime('%Y-%m-%d')
            })

        return jsonify({
            "status": "success",
            "data": templates_data
        }), 200

    except Exception as e:
        app.logger.error(f"获取模板列表失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

#获取模板详情
@app.route('/api/performance_templates/<int:template_id>', methods=['GET'])
@jwt_required()
def get_template_details(template_id):
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 查询模板
        template = PerformanceTemplate.query.filter_by(
            template_id=template_id,
            emp_number=employee.employee_number
        ).first()

        if not template:
            return jsonify({
                "status": "error",
                "message": "模板不存在或无权访问"
            }), 404

        # 返回模板详情
        return jsonify({
            "status": "success",
            "data": {
                "template_id": template.template_id,
                "template_name": template.template_name,
                "created_at": format_china_time(template.created_at),
                "updated_at": format_china_time(template.updated_at),
                "is_default": template.is_default,
                "template_data": template.template_data
            }
        }), 200

    except Exception as e:
        app.logger.error(f"获取模板详情失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

#更新模板
# 更新模板接口 - 添加详细日志
@app.route('/api/performance_templates/<int:template_id>', methods=['PUT'])
@jwt_required()
def update_performance_template(template_id):
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        data = request.get_json()
        app.logger.info(f"更新模板请求数据: {data}")  # 记录请求数据

        # 查询模板
        template = PerformanceTemplate.query.filter_by(
            template_id=template_id,
            emp_number=employee.employee_number
        ).first()

        # 记录原始更新时间
        if template:
            app.logger.info(f"原始更新时间: {template.updated_at}")

        if not template:
            app.logger.warning(f"模板不存在: template_id={template_id}, employee_number={employee.employee_number}")
            return jsonify({
                "status": "error",
                "message": "模板不存在或无权访问"
            }), 404

        # 更新模板名称
        if 'template_name' in data:
            app.logger.info(f"更新模板名称: {template.template_name} -> {data['template_name']}")
            template.template_name = data['template_name']

        # 更新模板数据
        if 'template_data' in data:
            # 验证数据结构
            if not isinstance(data['template_data'], dict) or 'items' not in data['template_data']:
                return jsonify({
                    "status": "error",
                    "message": "模板数据结构无效"
                }), 400

            app.logger.info(f"更新模板数据: 项目数={len(data['template_data'].get('items', []))}")
            template.template_data = data['template_data']

        # 更新默认状态
        if 'is_default' in data:
            app.logger.info(f"更新默认状态: {template.is_default} -> {data['is_default']}")
            # 如果设置为默认，先取消其他默认模板
            if data['is_default']:
                # 查找当前默认模板
                current_default = PerformanceTemplate.query.filter_by(
                    emp_number=employee.employee_number,
                    is_default=True
                ).first()

                if current_default:
                    app.logger.info(f"取消原默认模板: template_id={current_default.template_id}")

                # 取消所有默认模板
                PerformanceTemplate.query.filter_by(
                    emp_number=employee.employee_number,
                    is_default=True
                ).update({'is_default': False})

            template.is_default = data['is_default']

        # 确保更新时间字段被更新
        old_updated_at = template.updated_at
        template.updated_at = datetime.utcnow()

        app.logger.info(f"更新时间: {old_updated_at} -> {template.updated_at}")

        # 提交前检查
        db.session.flush()
        app.logger.info(f"提交前新更新时间: {template.updated_at}")

        db.session.commit()

        # 提交后检查
        committed_template = PerformanceTemplate.query.get(template_id)
        app.logger.info(f"提交后实际更新时间: {committed_template.updated_at}")

        return jsonify({
            "status": "success",
            "message": "模板更新成功",
            "data": {
                "template_id": template.template_id,
                "updated_at": template.updated_at.isoformat()
            }
        }), 200

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"更新模板失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
#删除模板
@app.route('/api/performance_templates/<int:template_id>', methods=['DELETE'])
@jwt_required()
def delete_performance_template(template_id):
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 查询模板
        template = PerformanceTemplate.query.filter_by(
            template_id=template_id,
            emp_number=employee.employee_number
        ).first()

        if not template:
            return jsonify({
                "status": "error",
                "message": "模板不存在或无权访问"
            }), 404

        # 删除关联的业绩记录
        PerformanceRecord.query.filter_by(
            template_id=template_id
        ).delete()

        # 删除模板
        db.session.delete(template)
        db.session.commit()

        return jsonify({
            "status": "success",
            "message": "模板删除成功"
        }), 200

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"删除模板失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# 提交业绩记录
@app.route('/api/performance_records', methods=['POST'])
@jwt_required()
def submit_performance_record():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = db.session.get(Employee, current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        data = request.get_json()

        # 验证必填字段
        required_fields = ['template_id', 'values_data']
        if not all(field in data for field in required_fields):
            return jsonify({
                "status": "error",
                "message": "缺少模板ID或业绩数据"
            }), 400

        # 检查模板是否存在
        template = db.session.get(PerformanceTemplate, data['template_id'])
        if not template or template.emp_number != employee.employee_number:
            return jsonify({
                "status": "error",
                "message": "模板不存在或无权访问"
            }), 404

        # 验证业绩数据
        values_data = data['values_data']
        if not isinstance(values_data, dict):
            return jsonify({
                "status": "error",
                "message": "业绩数据格式无效"
            }), 400

        # 生成业绩文案
        generated_text = generate_performance_text(template.template_data, values_data)

        # 获取中国当前日期和时间（带时区）
        china_time = get_china_time()
        china_date = china_time.date()

        # 检查当天是否已有记录
        existing_record = PerformanceRecord.query.filter(
            PerformanceRecord.emp_number == employee.employee_number,
            PerformanceRecord.template_id == data['template_id'],
            PerformanceRecord.record_date == china_date
        ).first()

        # 如果有当日记录则更新，否则创建新记录
        if existing_record:
            existing_record.values_data = values_data
            existing_record.generated_text = generated_text
            db.session.commit()

            return jsonify({
                "status": "success",
                "message": "业绩记录更新成功",
                "data": {
                    "record_id": existing_record.record_id,
                    "generated_text": generated_text,
                    "created_at": format_china_time(existing_record.created_at),
                    "record_date": china_date.strftime('%Y-%m-%d')
                }
            }), 200
        else:
            # 创建新业绩记录 - 使用完整的日期时间对象
            new_record = PerformanceRecord(
                template_id=data['template_id'],
                emp_number=employee.employee_number,
                values_data=values_data,
                generated_text=generated_text,
                record_date=china_date,
                created_at=china_time  # 存储完整的日期时间对象
            )
            db.session.add(new_record)
            db.session.commit()

            return jsonify({
                "status": "success",
                "message": "业绩记录提交成功",
                "data": {
                    "record_id": new_record.record_id,
                    "generated_text": generated_text,
                    "created_at": format_china_time(new_record.created_at),
                    "record_date": china_date.strftime('%Y-%m-%d')
                }
            }), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"提交业绩记录失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


def generate_performance_text(template_data, values_data):
    """根据模板和值生成业绩文案"""
    try:
        items = template_data.get('items', [])
        lines = []

        for item in items:
            item_id = str(item.get('id'))
            product_name = item.get('product_name', '')
            unit = item.get('unit', '')

            # 获取对应的值
            value = values_data.get(item_id, '0')

            # 构建行文本
            line = f"{product_name}：{value}{unit}"
            lines.append(line)

        return "\n".join(lines)

    except Exception as e:
        app.logger.error(f"生成业绩文案失败: {str(e)}")
        return "业绩文案生成失败，请检查数据格式"


# # 获取业绩记录历史
# @app.route('/api/performance_records', methods=['GET'])
# @jwt_required()
# def get_performance_records():
#     try:
#         # 获取当前用户身份
#         current_user_id = int(get_jwt_identity())
#         employee = db.session.get(Employee, current_user_id)
#         if not employee:
#             return jsonify({"status": "error", "message": "用户不存在"}), 404
#
#         # 获取查询参数
#         template_id = request.args.get('template_id')
#         page = request.args.get('page', 1, type=int)
#         size = request.args.get('size', 10, type=int)
#
#         # 构建查询
#         query = db.session.query(
#             PerformanceRecord,
#             PerformanceTemplate.template_name
#         ).join(
#             PerformanceTemplate,
#             PerformanceRecord.template_id == PerformanceTemplate.template_id
#         ).filter(
#             PerformanceRecord.emp_number == employee.employee_number
#         )
#
#         # 按模板筛选
#         if template_id:
#             query = query.filter(PerformanceRecord.template_id == template_id)
#
#         # 分页查询
#         pagination = query.order_by(
#             PerformanceRecord.created_at.desc()
#         ).paginate(page=page, per_page=size, error_out=False)
#
#         #         records = []
#         #         for record, template_name in pagination.items:
#         #             records.append({
#         #                 "record_id": record.record_id,
#         #                 "created_at": record.created_at.strftime('%Y-%m-%d %H:%M:%S'),
#         #                 "template_id": record.template_id,
#         #                 "template_name": template_name,  # 添加模板名称
#         #                 "record_date": record.record_date.isoformat(),
#         #                 "values_data": record.values_data,
#         #                 "generated_text": record.generated_text
#         #             })
#
#         records = []
#         for record, template_name in pagination.items:
#             records.append({
#                 "record_id": record.record_id,
#                 "created_at": record.created_at.strftime('%Y-%m-%d %H:%M:%S'),
#                 "template_id": record.template_id,
#                 "template_name": template_name,
#                 "record_date": record.record_date.isoformat(),
#                 "values_data": record.values_data,
#                 "generated_text": record.generated_text
#             })
#
#         return jsonify({
#             "status": "success",
#             "data": {
#                 "records": records,
#                 "pagination": {
#                     "page": page,
#                     "size": size,
#                     "total": pagination.total,
#                     "pages": pagination.pages
#                 }
#             }
#         }), 200
#
#     except Exception as e:
#         app.logger.error(f"获取业绩记录失败: {str(e)}", exc_info=True)
#         return jsonify({
#             "status": "error",
#             "message": "服务器内部错误"
#         }), 500

# 获取业绩记录历史
@app.route('/api/performance_records', methods=['GET'])
@jwt_required()
def get_performance_records():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = db.session.get(Employee, current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 获取查询参数
        template_id = request.args.get('template_id')
        today_str = request.args.get('today')
        page = request.args.get('page', 1, type=int)
        size = request.args.get('size', 10, type=int)
        has_date_filter = bool(today_str)  # 记录是否有日期过滤条件

        # 构建查询
        query = db.session.query(
            PerformanceRecord,
            PerformanceTemplate.template_name
        ).join(
            PerformanceTemplate,
            PerformanceRecord.template_id == PerformanceTemplate.template_id
        ).filter(
            PerformanceRecord.emp_number == employee.employee_number
        )

        # 按日期筛选
        if today_str:
            try:
                # 解析日期字符串
                target_date = datetime.strptime(today_str, "%Y-%m-%d").date()
                query = query.filter(PerformanceRecord.record_date == target_date)
            except ValueError:
                return jsonify({"status": "error", "message": "日期格式无效，请使用YYYY-MM-DD格式"}), 400

        # 按模板筛选
        if template_id:
            query = query.filter(PerformanceRecord.template_id == template_id)

        # 分页查询
        pagination = query.order_by(
            PerformanceRecord.created_at.desc()
        ).paginate(page=page, per_page=size, error_out=False)

        records = []
        for record, template_name in pagination.items:
            records.append({
                "record_id": record.record_id,
                "created_at": record.created_at.strftime('%Y-%m-%d %H:%M:%S'),
                "template_id": record.template_id,
                "template_name": template_name,
                "record_date": record.record_date.isoformat(),
                "values_data": record.values_data,
                "generated_text": record.generated_text
            })

        # 构建响应数据
        response_data = {
            "status": "success",
            "data": {
                "records": records,
                "pagination": {
                    "page": page,
                    "size": size,
                    "total": pagination.total,
                    "pages": pagination.pages
                }
            }
        }

        # 添加特殊提示：当日无业绩记录
        if has_date_filter and pagination.total == 0:
            response_data["data"]["message"] = f"{today_str} 当日无业绩记录"

        return jsonify(response_data), 200

    except Exception as e:
        app.logger.error(f"获取业绩记录失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# 检查今日是否已提交业绩记录
@app.route('/api/performance_records/today', methods=['GET'])
@jwt_required()
def check_today_record():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = db.session.get(Employee, current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 获取中国当前日期
        china_date = get_china_time().date()

        # 获取日期参数，默认为当天
        date_str = request.args.get('date')

        if date_str:
            record_date = datetime.strptime(date_str, '%Y-%m-%d').date()
        else:
            record_date = china_date

        # 查询当日记录
        record = db.session.query(
            PerformanceRecord,
            PerformanceTemplate.template_name
        ).join(
            PerformanceTemplate,
            PerformanceRecord.template_id == PerformanceTemplate.template_id
        ).filter(
            PerformanceRecord.emp_number == employee.employee_number,
            PerformanceRecord.record_date == record_date
        ).first()

        if record:
            performance_record, template_name = record

            #             return jsonify({
            #                 "status": "success",
            #                 "data": {
            #                     "exists": True,
            #                     "record_id": performance_record.record_id,
            #                     "generated_text": performance_record.generated_text,
            #                     "template_id": performance_record.template_id,
            #                     "template_name": template_name,  # 添加模板名称
            #                     "created_at": performance_record.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            #                     "record_date": performance_record.record_date.strftime('%Y-%m-%d')
            #                 }
            #             }), 200


            return jsonify({
                "status": "success",
                "data": {
                    "exists": True,
                    "record_id": performance_record.record_id,
                    "generated_text": performance_record.generated_text,
                    "template_id": performance_record.template_id,
                    "template_name": template_name,
                    "created_at": performance_record.created_at.strftime('%Y-%m-%d %H:%M:%S'),
                    "record_date": performance_record.record_date.strftime('%Y-%m-%d')
                }
            }), 200
        else:
            return jsonify({
                "status": "success",
                "data": {
                    "exists": False
                }
            }), 200

    except Exception as e:
        app.logger.error(f"检查今日记录失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
# ============== 业绩模板接口结束 ==============

# ============== 业绩统计接口开始 ==============
# 获取当前机构下的所有员工编号
@app.route('/api/org_employees', methods=['GET'])
@jwt_required()
def get_org_employees():
    try:
        # 记录开始
        app.logger.info(f"[org_employees] 请求开始")

        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)

        # 日志记录用户信息
        app.logger.info(f"[org_employees] 当前用户ID: {current_user_id}, 员工信息: {employee}")

        if not employee:
            app.logger.warning(f"[org_employees] 用户不存在: user_id={current_user_id}")
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 记录职位和机构代码
        app.logger.info(f"[org_employees] 用户职位: '{employee.job_title}', 机构代码: '{employee.organization_code}'")

        # 检查是否为营销主管且有机构代码
        if employee.job_title != "营销主管":
            app.logger.warning(f"[org_employees] 权限不足: 用户职位不是营销主管（当前职位: '{employee.job_title}'）")
            return jsonify({
                "status": "error",
                "message": f"无权限访问此资源，需要营销主管权限（当前职位: {employee.job_title})"
            }), 403

        if not employee.organization_code:
            app.logger.warning(f"[org_employees] 权限不足: 用户机构代码为空")
            return jsonify({
                "status": "error",
                "message": "无权限访问此资源，机构代码为空"
            }), 403

        # 获取相同机构的所有员工
        org_employees = Employee.query.filter_by(
            organization_code=employee.organization_code
        ).all()

        # 提取员工编号
        emp_numbers = [emp.employee_number for emp in org_employees]

        # 记录结果
        app.logger.info(
            f"[org_employees] 成功获取机构员工列表，机构代码: {employee.organization_code}, 员工数量: {len(emp_numbers)}")

        return jsonify({
            "status": "success",
            "data": emp_numbers
        }), 200

    except Exception as e:
        app.logger.error(f"[org_employees] 获取机构员工失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误",
            "error_details": str(e)
        }), 500


# # 业绩统计接口（优化版）（全统计完美版）
# @app.route('/api/performance_summary', methods=['GET'])
# @jwt_required()
# def get_performance_summary():
#     try:
#         app.logger.info("[业绩统计] 接口调用开始")
#
#         # 获取当前用户身份
#         current_user_id = int(get_jwt_identity())
#         app.logger.info(f"[业绩统计] 当前用户ID: {current_user_id}")
#
#         # 使用 Session.get() 避免警告
#         employee = db.session.get(Employee, current_user_id)
#         if not employee:
#             app.logger.warning(f"[业绩统计] 用户不存在: user_id={current_user_id}")
#             return jsonify({"status": "error", "message": "用户不存在"}), 404
#
#         # 验证是否为营销主管
#         app.logger.info(f"[业绩统计] 用户职位: '{employee.job_title}', 机构代码: '{employee.organization_code}'")
#         if employee.job_title != "营销主管" or not employee.organization_code:
#             app.logger.warning(f"[业绩统计] 权限不足: 用户职位不是营销主管或机构代码为空")
#             return jsonify({
#                 "status": "error",
#                 "message": "无权限访问此资源"
#             }), 403
#
#         # 验证参数
#         days = request.args.get('days', default=7, type=int)
#         template_name = request.args.get('template_name')
#         app.logger.info(f"[业绩统计] 请求参数: days={days}, template_name={template_name}")
#
#         if not template_name:
#             app.logger.warning("[业绩统计] 缺少模板名称参数")
#             return jsonify({
#                 "status": "error",
#                 "message": "缺少模板名称参数"
#             }), 400
#
#         # 使用record_date字段进行日期范围过滤（基于中国时区）
#         china_tz = pytz.timezone('Asia/Shanghai')
#         today_china = datetime.now(china_tz).date()
#         start_date = today_china - timedelta(days=days)
#         end_date = today_china
#
#         app.logger.info(f"[业绩统计] 查询业绩日期范围: {start_date} 至 {end_date}")
#
#         # 获取相同机构的所有员工
#         org_employees = Employee.query.filter_by(
#             organization_code=employee.organization_code
#         ).all()
#         emp_numbers = [emp.employee_number for emp in org_employees]
#         app.logger.info(f"[业绩统计] 机构员工编号: {emp_numbers}")
#
#         # 获取相同机构的所有模板（按名称过滤）
#         templates = PerformanceTemplate.query.filter(
#             PerformanceTemplate.emp_number.in_(emp_numbers),
#             PerformanceTemplate.template_name == template_name
#         ).all()
#
#         template_ids = [t.template_id for t in templates]
#         # 输出模板详情
#         template_details = [{"id": t.template_id, "emp_number": t.emp_number} for t in templates]
#         app.logger.info(f"[业绩统计] 模板名称 '{template_name}' 对应的模板: {template_details}")
#
#         if not template_ids:
#             app.logger.warning(f"[业绩统计] 没有找到模板名称为 '{template_name}' 的模板")
#             return jsonify({
#                 "status": "success",
#                 "data": [],
#                 "message": "该模板名称下没有业绩记录"
#             }), 200
#
#         # 获取业绩记录，使用record_date过滤
#         records = PerformanceRecord.query.filter(
#             PerformanceRecord.template_id.in_(template_ids),
#             PerformanceRecord.record_date >= start_date,
#             PerformanceRecord.record_date <= end_date
#         ).all()
#
#         app.logger.info(f"[业绩统计] 查询到的记录数量: {len(records)}")
#
#         if not records:
#             app.logger.warning("[业绩统计] 未找到匹配的业绩记录")
#             return jsonify({
#                 "status": "success",
#                 "data": [],
#                 "message": "该时间段内没有业绩记录"
#             }), 200
#
#         # 产品统计逻辑（基于generated_text）
#         product_summary = defaultdict(float)
#         processed_records = 0
#
#         # 重构的解析函数：按行解析产品名称和数量
#         def parse_generated_text(text):
#             """
#             优化解析generated_text中的产品数量信息
#             新策略：按行分割文本，每行识别一个产品
#             格式：产品名称 + 中文/英文冒号 + 数量（忽略单位）
#             示例："手机银行：5个" -> 产品:"手机银行", 数量:5
#             """
#             results = {}
#             lines = text.split('\n')
#             app.logger.debug(f"开始解析文本，共 {len(lines)} 行")
#
#             for line in lines:
#                 line = line.strip()
#                 if not line:
#                     continue
#
#                 # 查找产品名称和数量模式
#                 # 格式1：中文："产品名称：5个" 或 "产品名称：5"
#                 # 格式2：英文："product name:5"
#                 match = re.search(r'^([^:：\n]+?)[:：]\s*(\d+\.?\d*)', line)
#
#                 if match:
#                     product = match.group(1).strip()
#                     count_str = match.group(2)
#
#                     try:
#                         count = float(count_str)
#                         # 累加相同产品的数量
#                         results[product] = results.get(product, 0) + count
#                         app.logger.debug(f"匹配成功: '{product}' = {count}")
#                     except ValueError:
#                         app.logger.warning(f"数值转换失败: 产品 '{product}', 值 '{count_str}'")
#                 else:
#                     # 尝试更宽松的匹配模式：产品名称后紧跟数字
#                     match = re.search(r'^([\u4e00-\u9fa5a-zA-Z0-9\s]+)\s+(\d+\.?\d*)', line)
#                     if match:
#                         product = match.group(1).strip()
#                         count_str = match.group(2)
#                         try:
#                             count = float(count_str)
#                             results[product] = results.get(product, 0) + count
#                             app.logger.debug(f"宽松匹配成功: '{product}' = {count}")
#                         except ValueError:
#                             app.logger.warning(f"宽松匹配数值转换失败: 产品 '{product}', 值 '{count_str}'")
#                     else:
#                         app.logger.debug(f"未匹配到产品行: {line[:50]}{'...' if len(line) > 50 else ''}")
#
#             return results
#
#         # 处理每条记录
#         for record in records:
#             try:
#                 generated_text = record.generated_text
#                 app.logger.debug(f"Record ID: {record.record_id}, generated_text:\n{generated_text[:200]}...")
#
#                 # 解析文本中的产品信息
#                 products = parse_generated_text(generated_text)
#
#                 # 累加到总统计
#                 for product, count in products.items():
#                     product_summary[product] += count
#
#                 processed_records += 1
#
#             except Exception as e:
#                 app.logger.error(f"处理记录失败: record_id={record.record_id}, error={str(e)}", exc_info=True)
#
#         app.logger.info(f"[业绩统计] 成功处理记录数: {processed_records}/{len(records)}")
#
#         # 将统计结果转换为前端需要的格式 - 确保返回整数
#         result = []
#         for product, total in product_summary.items():
#             # 关键修改：将浮点数转换为整数
#             result.append({
#                 "product_name": product,
#                 "total_count": int(total)  # 使用int()转换为整数
#             })
#
#         app.logger.info(f"[业绩统计] 统计结果: {result}")
#
#         return jsonify({
#             "status": "success",
#             "data": result
#         }), 200
#
#     except Exception as e:
#         app.logger.error(f"[业绩统计] 接口执行失败: {str(e)}", exc_info=True)
#         return jsonify({
#             "status": "error",
#             "message": "服务器内部错误"
#         }), 500


# 业绩统计接口（优化版）（全统计完美版）
@app.route('/api/performance_summary', methods=['GET'])
@jwt_required()
def get_performance_summary():
    try:
        app.logger.info("[业绩统计] 接口调用开始")

        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        app.logger.info(f"[业绩统计] 当前用户ID: {current_user_id}")

        # 使用 Session.get() 避免警告
        employee = db.session.get(Employee, current_user_id)
        if not employee:
            app.logger.warning(f"[业绩统计] 用户不存在: user_id={current_user_id}")
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 验证是否为营销主管
        app.logger.info(f"[业绩统计] 用户职位: '{employee.job_title}', 机构代码: '{employee.organization_code}'")
        if employee.job_title != "营销主管" or not employee.organization_code:
            app.logger.warning(f"[业绩统计] 权限不足: 用户职位不是营销主管或机构代码为空")
            return jsonify({
                "status": "error",
                "message": "无权限访问此资源"
            }), 403

        # 验证参数
        days = request.args.get('days', default=7, type=int)
        template_name = request.args.get('template_name')
        emp_number = request.args.get('emp_number')  # 新增员工编号参数
        app.logger.info(f"[业绩统计] 请求参数: days={days}, template_name={template_name}")

        if not template_name:
            app.logger.warning("[业绩统计] 缺少模板名称参数")
            return jsonify({
                "status": "error",
                "message": "缺少模板名称参数"
            }), 400

        # 使用record_date字段进行日期范围过滤（基于中国时区）
        china_tz = pytz.timezone('Asia/Shanghai')
        today_china = datetime.now(china_tz).date()
        start_date = today_china - timedelta(days=days)
        end_date = today_china

        app.logger.info(f"[业绩统计] 查询业绩日期范围: {start_date} 至 {end_date}")

        # 获取相同机构的所有员工
        org_employees = Employee.query.filter_by(
            organization_code=employee.organization_code
        ).all()
        emp_numbers = [emp.employee_number for emp in org_employees]
        app.logger.info(f"[业绩统计] 机构员工编号: {emp_numbers}")

        # 如果有员工编号参数，则只筛选该员工
        emp_numbers = [emp.employee_number for emp in org_employees]
        if emp_number:
            # 验证员工是否属于当前机构
            if emp_number not in emp_numbers:
                return jsonify({
                    "status": "error",
                    "message": "该员工不属于当前机构"
                }), 400
            emp_numbers = [emp_number]

        # 获取相同机构的所有模板（按名称过滤）
        templates = PerformanceTemplate.query.filter(
            PerformanceTemplate.emp_number.in_(emp_numbers),
            PerformanceTemplate.template_name == template_name
        ).all()

        template_ids = [t.template_id for t in templates]
        # 输出模板详情
        template_details = [{"id": t.template_id, "emp_number": t.emp_number} for t in templates]
        app.logger.info(f"[业绩统计] 模板名称 '{template_name}' 对应的模板: {template_details}")

        if not template_ids:
            app.logger.warning(f"[业绩统计] 没有找到模板名称为 '{template_name}' 的模板")
            return jsonify({
                "status": "success",
                "data": [],
                "message": "该模板名称下没有业绩记录"
            }), 200

        # 获取业绩记录，使用record_date过滤
        records = PerformanceRecord.query.filter(
            PerformanceRecord.template_id.in_(template_ids),
            PerformanceRecord.record_date >= start_date,
            PerformanceRecord.record_date <= end_date
        ).all()

        app.logger.info(f"[业绩统计] 查询到的记录数量: {len(records)}")

        if not records:
            app.logger.warning("[业绩统计] 未找到匹配的业绩记录")
            return jsonify({
                "status": "success",
                "data": [],
                "message": "该时间段内没有业绩记录"
            }), 200

        # 产品统计逻辑（基于generated_text）
        product_summary = defaultdict(float)
        processed_records = 0

        # 重构的解析函数：按行解析产品名称和数量
        def parse_generated_text(text):
            """
            优化解析generated_text中的产品数量信息
            新策略：按行分割文本，每行识别一个产品
            格式：产品名称 + 中文/英文冒号 + 数量（忽略单位）
            示例："手机银行：5个" -> 产品:"手机银行", 数量:5
            """
            results = {}
            lines = text.split('\n')
            app.logger.debug(f"开始解析文本，共 {len(lines)} 行")

            for line in lines:
                line = line.strip()
                if not line:
                    continue

                # 查找产品名称和数量模式
                # 格式1：中文："产品名称：5个" 或 "产品名称：5"
                # 格式2：英文："product name:5"
                match = re.search(r'^([^:：\n]+?)[:：]\s*(\d+\.?\d*)', line)

                if match:
                    product = match.group(1).strip()
                    count_str = match.group(2)

                    try:
                        count = float(count_str)
                        # 累加相同产品的数量
                        results[product] = results.get(product, 0) + count
                        app.logger.debug(f"匹配成功: '{product}' = {count}")
                    except ValueError:
                        app.logger.warning(f"数值转换失败: 产品 '{product}', 值 '{count_str}'")
                else:
                    # 尝试更宽松的匹配模式：产品名称后紧跟数字
                    match = re.search(r'^([\u4e00-\u9fa5a-zA-Z0-9\s]+)\s+(\d+\.?\d*)', line)
                    if match:
                        product = match.group(1).strip()
                        count_str = match.group(2)
                        try:
                            count = float(count_str)
                            results[product] = results.get(product, 0) + count
                            app.logger.debug(f"宽松匹配成功: '{product}' = {count}")
                        except ValueError:
                            app.logger.warning(f"宽松匹配数值转换失败: 产品 '{product}', 值 '{count_str}'")
                    else:
                        app.logger.debug(f"未匹配到产品行: {line[:50]}{'...' if len(line) > 50 else ''}")

            return results

        # 处理每条记录
        for record in records:
            try:
                generated_text = record.generated_text
                app.logger.debug(f"Record ID: {record.record_id}, generated_text:\n{generated_text[:200]}...")

                # 解析文本中的产品信息
                products = parse_generated_text(generated_text)

                # 累加到总统计
                for product, count in products.items():
                    product_summary[product] += count

                processed_records += 1

            except Exception as e:
                app.logger.error(f"处理记录失败: record_id={record.record_id}, error={str(e)}", exc_info=True)

        app.logger.info(f"[业绩统计] 成功处理记录数: {processed_records}/{len(records)}")

        # 将统计结果转换为前端需要的格式 - 确保返回整数
        result = []
        for product, total in product_summary.items():
            # 关键修改：将浮点数转换为整数
            result.append({
                "product_name": product,
                "total_count": int(total)  # 使用int()转换为整数
            })

        app.logger.info(f"[业绩统计] 统计结果: {result}")

        return jsonify({
            "status": "success",
            "data": result
        }), 200

    except Exception as e:
        app.logger.error(f"[业绩统计] 接口执行失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# 临时调试路由 - 检查业绩记录数据结构
@app.route('/api/debug/performance_records', methods=['GET'])
@jwt_required()
def debug_performance_records():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)

        # 查询用户的所有业绩记录
        records = PerformanceRecord.query.filter_by(
            emp_number=employee.employee_number
        ).order_by(PerformanceRecord.record_date.desc()).limit(10).all()

        results = []
        for record in records:
            record_data = {
                "record_id": record.record_id,
                "template_id": record.template_id,
                "record_date": record.record_date.isoformat(),
                "created_at": record.created_at.isoformat(),
                "values_data_type": str(type(record.values_data))
            }

            # 尝试解析JSON数据
            try:
                if isinstance(record.values_data, str):
                    record_data["values_data"] = json.loads(record.values_data)
                else:
                    record_data["values_data"] = record.values_data
            except Exception as e:
                record_data["values_data_error"] = str(e)

            results.append(record_data)

        return jsonify({
            "status": "success",
            "data": results
        }), 200

    except Exception as e:
        return jsonify({
            "status": "error",
            "message": str(e)
        }), 500

# 获取机构下所有模板名称（去重）
@app.route('/api/org_templates', methods=['GET'])
@jwt_required()
def get_org_templates():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 检查是否为营销主管且有机构代码
        if employee.job_title != "营销主管" or not employee.organization_code:
            return jsonify({
                "status": "error",
                "message": "无权限访问此资源"
            }), 403

        # 获取相同机构的所有员工
        org_employees = Employee.query.filter_by(
            organization_code=employee.organization_code
        ).all()
        emp_numbers = [emp.employee_number for emp in org_employees]

        # 查询这些员工的所有模板
        templates = PerformanceTemplate.query.filter(
            PerformanceTemplate.emp_number.in_(emp_numbers)
        ).all()

        # 去重模板名称
        unique_template_names = {}
        for template in templates:
            unique_template_names[template.template_name] = {
                "template_name": template.template_name,
                "template_id": template.template_id
            }

        # 转换为列表
        template_list = list(unique_template_names.values())

        return jsonify({
            "status": "success",
            "data": template_list
        }), 200

    except Exception as e:
        app.logger.error(f"获取机构模板失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500


# 获取当前用户所在机构的员工列表
@app.route('/api/employees_by_org', methods=['GET'])
@jwt_required()
def get_employees_by_org():
    try:
        # 获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # 获取当前用户所在机构的所有员工
        employees = Employee.query.filter_by(
            organization_code=employee.organization_code
        ).all()

        # 格式化返回数据
        employees_data = []
        for emp in employees:
            employees_data.append({
                "employee_number": emp.employee_number,
                "name": emp.name
            })

        return jsonify({
            "status": "success",
            "data": employees_data
        }), 200

    except Exception as e:
        app.logger.error(f"获取员工列表失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# ============== 业绩统计接口结束 ==============

# 定时任务：发送短信
def check_expired_customers():
    with app.app_context():
        today = datetime.now().date()
        customers = Customer.query.filter(
            Customer.expire_date == today,
            Customer.notified == False
        ).all()

        for customer in customers:
            # 示例：打印日志模拟发送短信
            print(f"[短信] 发送给 {customer.phone}: {customer.name}，您的存款{customer.amount}元已到期")
            customer.notified = True
            db.session.commit()

            ######测试发送短信######
            # 调用短信发送函数
            success = send_sms(
                phone=customer.phone,  # 需要从数据库或Excel中获取手机号字段
                name=customer.name,
                amount=customer.amount,
                expire_date=str(customer.expire_date)
            )
            if success:
                customer.notified = True
                db.session.commit()
                print(f"[成功] 短信已发送给 {customer.phone}")
            else:
                print(f"[失败] 短信发送失败 {customer.phone}")
            ######测试发送短信######


# 启动定时任务（每天9点执行）
scheduler = BackgroundScheduler()
# 临时修改定时任务为立即执行，测试时候执行
scheduler.add_job(check_expired_customers, 'date', run_date=datetime.now())
# 上线时执行定时任务（每天9点执行）
# scheduler.add_job(check_expired_customers, 'cron', hour=9)
scheduler.start()

if __name__ == '__main__':
    with app.app_context():
        db.create_all()  # 创建数据库表
    app.run(host='0.0.0.0', port=5001, debug=True)
